﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Net;
using System.Net.Mail;
using System.Text.RegularExpressions;

/// <summary>
/// Summary description for clsThaoTac
/// </summary>
public class clsThaoTac
{
    SoLienLacDataContext db = new SoLienLacDataContext();
	public clsThaoTac()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public bool CheckEmail(string Email)
    {
        bool check = true;

        Regex regex = new Regex(@"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*");
        if (regex.IsMatch(Email) == false)
        {
            check = false;
        }
        return check;
    }
    public string LamTronDiem(string diem)
    {
        string kq = "";
        char[] kitu = diem.ToCharArray();
        if (kitu.Length <= 3)
        {

            kq = diem;
        }
        if (kitu.Length >= 4)
        {
            string con = diem.Substring(0, 4);
            int d = int.Parse(con.Substring(3));
            if (d >= 5 && d <= 9)
            {

                kq = (double.Parse(diem.Substring(0, 3)) + 0.1).ToString();
            }
            if (d < 5 && d >= 0)
            {
                kq = diem.Substring(0, 3);
            }

        }
        char[] kt = kq.ToCharArray();
        if (kt.Length == 1)
        { kq = kq + ".0"; }

        return kq;
    }

    public void XetDiemNgoaiKhoang(string diem, ref int tong, double max)
    {
        if (diem != "")
        {
            if (float.Parse(diem) < max)
            {
                tong = tong + 1;
            }
        }
    }
    public string DanhGiaHocLuc(float dtb, string dtoan1, string dvan1, DataRow row)
    {
        string row16 = "";
        if (dtoan1 != "" && dvan1 != "")
        {
            double dtoan = double.Parse(dtoan1);
            double dvan = double.Parse(dvan1);
            if (dtb >= 8.0)
            {
                if (dtoan >= 8.0 || dvan >= 8.0)
                {
                    int tong = 0;
                    for (int i = 23; i <= 15; i++)
                    {
                        if (row[i].ToString() != "")
                        {
                            XetDiemNgoaiKhoang(row[i].ToString(), ref tong, 6.5);
                        }
                    }
                    if (tong == 0)
                    { row16 = "Giỏi"; }
                    if (tong > 0)
                    { row16 = "Khá"; }
                }
                if (dtoan < 8.0 && dvan < 8.0)
                { row16 = "Khá"; }
            }
            if (dtb >= 6.5 && dtb <8.0)
            {

                if (dtoan >= 6.5 || dvan >= 6.5)
                {
                    int tong = 0;
                    for (int i = 3; i <= 15; i++)
                    {
                        if (row[i].ToString() != "")
                        {
                            XetDiemNgoaiKhoang(row[i].ToString(), ref tong, 5.0);
                        }
                    }
                    if (tong == 0)
                    { row16 = "Khá"; }
                    if (tong > 0) { row16 = "TB"; }
                }
                if (dtoan <= 6.4 && dvan <= 6.4)
                { row16 = "TB"; }
            }
            if (dtb >= 5.0 && dtb <6.5)
            {

                if (dtoan >= 5.0 || dvan >= 5.0)
                {
                    int tong = 0;
                    for (int i = 3; i <= 15; i++)
                    {
                        if (row[i].ToString() != "")
                        {
                            XetDiemNgoaiKhoang(row[i].ToString(), ref tong, 3.5);
                        }
                    }
                    if (tong == 0)
                    { row16 = "TB"; }
                    if (tong > 0) { row16 = "Yếu"; }
                }
                if (dtoan <= 4.9 && dvan <= 4.9)
                { row16 = "Yếu"; }

            }
            if (dtb >= 3.5 && dtb < 5.0)
            {

                if (dtoan >= 5.0 || dvan >= 5.0)
                {
                    int tong = 0;
                    for (int i = 3; i <= 15; i++)
                    {
                        if (row[i].ToString() != "")
                        {
                            XetDiemNgoaiKhoang(row[i].ToString(), ref tong, 2.0);
                        }
                    }
                    if (tong == 0)
                    { row16 = "Yếu"; }
                    if (tong > 0) { row16 = "Kém"; }
                }
                if (dtoan <= 3.4 && dvan <= 3.4)
                { row16 = "Kém"; }
            }
            if (dtb < 2.0)
            {
                row16 = "Kém";
            }

        }
        return row16;
    }
    public string TinhDiemCaNam(string diem1, string diem2)
    {
        string diemTBCN = "";
        if (diem1 != "" && diem2 != "")
        {

            float diem = (float.Parse(diem1) + float.Parse(diem2) * 2) / 3;
            diemTBCN = LamTronDiem(diem.ToString());
        }
        if (diem1 != "" && diem2 == "")
        {
            diemTBCN = diem1.ToString();
        }
        if (diem1 == "" && diem2 != "")
        {
            diemTBCN = diem2.ToString();
        }

        return diemTBCN;
    }
    public string tmptinhDiemTBTCN(int manam, int mamon, string mahs, ref float tongdiem, ref int tongheso)
    {
        string dtb = "";
        var d1_1 = from p in db.funcTraVeDTBCuaTungMonHoc(manam, 1, mamon, mahs) select p.DTBMonHocKy;
        var d1_2 = from p in db.funcTraVeDTBCuaTungMonHoc(manam, 2, mamon, mahs) select p.DTBMonHocKy;
        if (d1_1.Count() != 0 && d1_2.Count() != 0)
        {
            dtb = TinhDiemCaNam(d1_1.First().ToString(), d1_2.First().ToString());

            if (dtb != "")
            {
                int hs1 = (int)db.funcTraVeHeSoMonHoc(manam, mahs, 1);

                tongdiem = tongdiem + float.Parse(dtb) * hs1;
                //Response.Write(tongdiem);
                tongheso = tongheso + hs1;
            }
        }
        if (d1_1.Count() != 0 && d1_2.Count() == 0)
        {
            dtb = d1_1.First().ToString();
            if (dtb != "")
            {
                int hs1 = (int)db.funcTraVeHeSoMonHoc(manam, mahs, 1);

                tongdiem = tongdiem + float.Parse(dtb) * hs1;
                tongheso = tongheso + hs1;
            }
        }
        if (d1_1.Count() == 0 && d1_2.Count() != 0)
        {
            dtb = d1_2.First().ToString();
            if (dtb != "")
            {
                int hs1 = (int)db.funcTraVeHeSoMonHoc(manam, mahs, 1);

                tongdiem = tongdiem + float.Parse(dtb) * hs1;
                tongheso = tongheso + hs1;
            }
        }
        if (d1_1.Count() == 0 && d1_2.Count() == 0)
        {
            dtb = "";
        }
        return dtb;
    }

    public void LoadDiemChiTiet(int manam, int maky, string mahs, GridView grv, Label lblDTB, Label lblHK, Label lblHL)
    {
        grv.DataSource = db.funcTraVeBangDiemCuaHS(manam, maky, mahs);
        grv.DataBind();
        var c = from p in db.KetQuaTongHops
                where p.MaNam == manam && p.MaKy == maky && p.MaHS == mahs
                select  p.HanhKiem ;
        var c1 = from p in db.KetQuaTongHops
                 where p.MaNam == manam && p.MaKy == maky && p.MaHS == mahs
                 select p.DiemTongKet;
        if (c1.Count() != 0)
        {
            lblDTB.Text = c1.First().ToString();
            if (c1.First() != null)
            {
                lblHK.Text = c.First();
            }
            lblHL.Text = DanhGiaHocLuc1(float.Parse(lblDTB.Text));
        }
        if (c1.Count() == 0)
        {
            lblDTB.Text = "";
            lblHK.Text = "";
            lblHL.Text = "";
        }


    }
    private string DanhGiaHocLuc1(float dtb)
    {
        string hl = "";
        if (dtb >= 8.0)
        {
            hl = "Giỏi";

        }
        if (dtb >= 6.5 && dtb < 8.0)
        {
            hl = "Khá";
        }
        if (dtb >= 5.0 && dtb < 6.5)
        {
            hl = "TB";
        }
        if (dtb >= 3.5 && dtb < 5.0)
        {
            hl = "Yếu";
        }
        if (dtb < 3.5)
        {
            hl = "Kém";
        }
        return hl;
    }
    public  void LoadGridCaNam(int manam, string mahs, GridView grv)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("TenMon", typeof(string));
        dt.Columns.Add("DTBMonHocKy1", typeof(string));
        dt.Columns.Add("DTBMonHocKy2", typeof(string));
        dt.Columns.Add("DTBMonCaNam", typeof(string));
        var c = from p in db.MonHocs select new { p.MaMon, p.TenMon };
        foreach (var con in c)
        {
            DataRow row = dt.NewRow();
            row[0] = con.TenMon;
            var c1 = from p in db.Diems
                     where p.MaNam == manam && p.MaKy == 1 && p.MaHS == mahs && p.MaMon == con.MaMon
                     select p.DTBMonHocKy;
            if (c1.Count() != 0)
            {
                row[1] = db.funcTraVeDTBMonHK(manam, 1, con.MaMon, mahs);
            }
            var c2 = from p in db.Diems
                     where p.MaNam == manam && p.MaKy == 2 && p.MaMon == con.MaMon && p.MaHS == mahs
                     select p.DTBMonHocKy;
            if (c2.Count() != 0)
            { row[2] = db.funcTraVeDTBMonHK(manam, 2, con.MaMon, mahs); }
            if (row[1].ToString() != "" && row[2].ToString() != "")
            {
                string dtbcn = "";
                float dtk = (float.Parse(row[1].ToString()) + float.Parse(row[2].ToString()) * 2) / 3;
                dtbcn = LamTronDiem(dtk.ToString());
                row[3] = dtbcn;

            }
            dt.Rows.Add(row);
        }
        grv.DataSource = dt;
        grv.DataBind();

    }

    public bool SendEmail(string ToEmail, string Subject, string BodyHTML, out string ErrorMessase)
    {
        bool check = true;
        ErrorMessase = "";
        MailMessage mail = new MailMessage("samngocpxhn.slldt@gmail.com", ToEmail);

        mail.Subject = Subject;
        mail.IsBodyHtml = true;
        string strBody = BodyHTML;
        mail.Body = strBody;
        mail.Bcc.Add(new MailAddress("samngocpxhn.slldt@gmail.com"));
        try
        {
            //smtp: dạng như kiểu người đưa thư
            //giao thức truyền, nhận thư điện tử căn bản
            //ssl: secure socket layer: thiết lập bảo mật máy chủ và máy khách
            SmtpClient Client = new SmtpClient();
            Client.DeliveryMethod = SmtpDeliveryMethod.Network;
            Client.EnableSsl = true;
            Client.Host = "smtp.gmail.com";
            Client.Port = 587;
            Client.UseDefaultCredentials = false;
            //credential: Giấy ủy nhiệm, ủy nhiệm cho phép sử dụng hệ thống
            NetworkCredential Credential = new NetworkCredential("samngocpxhn.slldt@gmail.com", "01021995");
            Client.Credentials = Credential;
            Client.Send(mail);
            check = true;
        }
        catch (Exception ex)
        {
            ErrorMessase = ex.Message;
            check = false;
        }
        return check;
    }

   public  string DanhGiaKhenThuong(string hl, string hk)
    {
        string danhhieu = "";
        if (hl == "Giỏi" && hk == "T")
        {
            danhhieu = "HS Giỏi";
        }
        if (hl == "Khá" && (hk == "T" || hk == "K"))
        {
            danhhieu = "HS Tiên tiến";
        }
        return danhhieu;
    }
   public void SoLuongXepLoaiHocLucKy(int manam,int malop,int maky,Label lblGioi,Label lblKha,Label lblTB,Label lblYeu,Label lblKem)
   {
       lblGioi.Text = "";
       lblKha.Text = "";
       lblTB.Text = "";
       lblKem.Text = "";
       lblYeu.Text = "";
       int gioi = int.Parse(db.funcDemSoHSXLTrongKy(manam, malop, maky, "Giỏi").ToString());
       int kha = int.Parse(db.funcDemSoHSXLTrongKy(manam, malop, maky, "Khá").ToString());
       int tb = int.Parse(db.funcDemSoHSXLTrongKy(manam, malop, maky, "TB").ToString());
       int yeu = int.Parse(db.funcDemSoHSXLTrongKy(manam, malop, maky, "Yếu").ToString());
       int kem = int.Parse(db.funcDemSoHSXLTrongKy(manam, malop, maky, "Kém").ToString());
       lblGioi.Text = gioi.ToString();
       lblKha.Text = kha.ToString();
       lblTB.Text = tb.ToString();
       lblYeu.Text = yeu.ToString();
       lblKem.Text = kem.ToString();
   }
   public void SoLuongXepLoaiCaNam(int manam, int malop, Label lblGioi, Label lblKha, Label lblTB, Label lblYeu, Label lblKem)
   {
       lblGioi.Text = "";
       lblKha.Text = "";
       lblTB.Text = "";
       lblKem.Text = "";
       lblYeu.Text = "";
       int gioi = int.Parse(db.funcDemSoHSXLCaNam(manam, malop, "Giỏi").ToString());
       int kha = int.Parse(db.funcDemSoHSXLCaNam(manam, malop, "Khá").ToString());
       int tb = int.Parse(db.funcDemSoHSXLCaNam(manam, malop, "TB").ToString());
       int yeu = int.Parse(db.funcDemSoHSXLCaNam(manam, malop, "Yếu").ToString());
       int kem = int.Parse(db.funcDemSoHSXLCaNam(manam, malop, "Kém").ToString());
       lblGioi.Text = gioi.ToString();
       lblKha.Text = kha.ToString();
       lblTB.Text = tb.ToString();
       lblYeu.Text = yeu.ToString();
       lblKem.Text = kem.ToString();
   }
   public void Export(DataTable dt, string sheetName, string title)
   {
       //Tạo các đối tượng Excel

       Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
       Microsoft.Office.Interop.Excel.Workbooks oBooks;
       Microsoft.Office.Interop.Excel.Sheets oSheets;
       Microsoft.Office.Interop.Excel.Workbook oBook;
       Microsoft.Office.Interop.Excel.Worksheet oSheet;

       //Tạo mới một Excel WorkBook
       oExcel.Visible = true;
       oExcel.DisplayAlerts = false;
       oExcel.Application.SheetsInNewWorkbook = 1;
       oBooks = oExcel.Workbooks;

       oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
       oSheets = oBook.Worksheets;
       oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
       oSheet.Name = sheetName;

       // Tạo phần đầu nếu muốn
       Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "S1");
       head.MergeCells = true;
       head.Value2 = title;
       head.Font.Bold = true;
       head.Font.Name = "Tahoma";
       head.Font.Size = "15";
       head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

       // Tạo tiêu đề cột
       Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
       cl1.Value2 = "STT";
       cl1.ColumnWidth = 5.0;

       Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
       cl2.Value2 = "Mã học sinh";
       cl2.ColumnWidth = 20.0;

       Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");
       cl3.Value2 = "Tên học sinh";
       cl3.ColumnWidth = 30.0;
       //----------DM--------//
       Microsoft.Office.Interop.Excel.Range clM1 = oSheet.get_Range("D3", "D3");
       clM1.Value2 = "M1";
       clM1.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clM2 = oSheet.get_Range("E3", "E3");
       clM2.Value2 = "M2";
       clM2.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clM3 = oSheet.get_Range("F3", "F3");
       clM3.Value2 = "M3";
       clM3.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clM4 = oSheet.get_Range("G3", "G3");
       clM4.Value2 = "M4";
       clM4.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clM5 = oSheet.get_Range("H3", "H3");
       clM5.Value2 = "M5";
       clM5.ColumnWidth = 5.0;
       //-----d15------//
       Microsoft.Office.Interop.Excel.Range cl15_1 = oSheet.get_Range("I3", "I3");
       cl15_1.Value2 = "15'1";
       cl15_1.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl15_2 = oSheet.get_Range("J3", "J3");
       cl15_2.Value2 = "15'2";
       cl15_2.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl15_3 = oSheet.get_Range("K3", "K3");
       cl15_3.Value2 = "15'3";
       cl15_3.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl15_4 = oSheet.get_Range("L3", "L3");
       cl15_4.Value2 = "15'4";
       cl15_4.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl15_5 = oSheet.get_Range("M3", "M3");
       cl15_5.Value2 = "15'5";
       cl15_5.ColumnWidth = 5.0;
       //-----d45---------//
       Microsoft.Office.Interop.Excel.Range cl45_1 = oSheet.get_Range("N3", "N3");
       cl45_1.Value2 = "45'1";
       cl45_1.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl45_2 = oSheet.get_Range("O3", "O3");
       cl45_2.Value2 = "45'2";
       cl45_2.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl45_3 = oSheet.get_Range("P3", "P3");
       cl45_3.Value2 = "45'3";
       cl45_3.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl45_4 = oSheet.get_Range("Q3", "Q3");
       cl45_4.Value2 = "45'4";
       cl45_4.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clHK = oSheet.get_Range("R3", "R3");
       clHK.Value2 = "HK";
       clHK.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clDTB = oSheet.get_Range("S3", "S3");
       clDTB.Value2 = "DTB";
       clDTB.ColumnWidth = 5.0;


       Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "S3");
       rowHead.Font.Bold = true;
       // Kẻ viền
       rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
       // Thiết lập màu nền
       rowHead.Interior.ColorIndex = 15;
       rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;


       // Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
       // vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
       object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];

       //Chuyển dữ liệu từ DataTable vào mảng đối tượng
       for (int r = 0; r < dt.Rows.Count; r++)
       {
           DataRow dr = dt.Rows[r];
           for (int c = 0; c < dt.Columns.Count; c++)
           {
               arr[r, c] = dr[c];
           }
       }

       //Thiết lập vùng điền dữ liệu
       int rowStart = 4;
       int columnStart = 1;

       int rowEnd = rowStart + dt.Rows.Count - 1;
       int columnEnd = dt.Columns.Count;

       // Ô bắt đầu điền dữ liệu
       Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
       // Ô kết thúc điền dữ liệu
       Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
       // Lấy về vùng điền dữ liệu
       Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);

       //Điền dữ liệu vào vùng đã thiết lập
       range.Value2 = arr;

       // Kẻ viền
       range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
       // Căn giữa cột STT
       Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
       Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
       oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

   }
   public DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query)
   {
       if (query == null)
       {
           throw new ArgumentNullException("query");
       }

       IDbCommand cmd = ctx.GetCommand(query as IQueryable);
       SqlDataAdapter adapter = new SqlDataAdapter();
       adapter.SelectCommand = (SqlCommand)cmd;
       DataTable dt = new DataTable("sd");

       try
       {
           cmd.Connection.Open();
           adapter.FillSchema(dt, SchemaType.Source);
           adapter.Fill(dt);
       }
       finally
       {
           cmd.Connection.Close();
       }
       return dt;
   }
   public void Export1(DataTable dt, string sheetName, string title)
   {
       //Tạo các đối tượng Excel

       Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
       Microsoft.Office.Interop.Excel.Workbooks oBooks;
       Microsoft.Office.Interop.Excel.Sheets oSheets;
       Microsoft.Office.Interop.Excel.Workbook oBook;
       Microsoft.Office.Interop.Excel.Worksheet oSheet;

       //Tạo mới một Excel WorkBook
       oExcel.Visible = true;
       oExcel.DisplayAlerts = false;
       oExcel.Application.SheetsInNewWorkbook = 1;
       oBooks = oExcel.Workbooks;

       oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
       oSheets = oBook.Worksheets;
       oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
       oSheet.Name = sheetName;

       // Tạo phần đầu nếu muốn
       Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "V1");
       head.MergeCells = true;
       head.Value2 = title;
       head.Font.Bold = true;
       head.Font.Name = "Tahoma";
       head.Font.Size = "15";
       head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

       // Tạo tiêu đề cột
       Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
       cl1.Value2 = "STT";
       cl1.ColumnWidth = 5.0;

       Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
       cl2.Value2 = "Mã học sinh";
       cl2.ColumnWidth = 20.0;

       Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");
       cl3.Value2 = "Tên học sinh";
       cl3.ColumnWidth = 30.0;
       //----------DM--------//
       Microsoft.Office.Interop.Excel.Range clM1 = oSheet.get_Range("D3", "D3");
       clM1.Value2 = "Toán";
       clM1.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clM2 = oSheet.get_Range("E3", "E3");
       clM2.Value2 = "Lý";
       clM2.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clM3 = oSheet.get_Range("F3", "F3");
       clM3.Value2 = "Hóa";
       clM3.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clM4 = oSheet.get_Range("G3", "G3");
       clM4.Value2 = "Sinh";
       clM4.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clM5 = oSheet.get_Range("H3", "H3");
       clM5.Value2 = "Tin";
       clM5.ColumnWidth = 5.0;
       //-----d15------//
       Microsoft.Office.Interop.Excel.Range cl15_1 = oSheet.get_Range("I3", "I3");
       cl15_1.Value2 = "Văn";
       cl15_1.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl15_2 = oSheet.get_Range("J3", "J3");
       cl15_2.Value2 = "Sử";
       cl15_2.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl15_3 = oSheet.get_Range("K3", "K3");
       cl15_3.Value2 = "Địa";
       cl15_3.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl15_4 = oSheet.get_Range("L3", "L3");
       cl15_4.Value2 = "NN";
       cl15_4.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl15_5 = oSheet.get_Range("M3", "M3");
       cl15_5.Value2 = "GDCD";
       cl15_5.ColumnWidth = 5.0;
       //-----d45---------//
       Microsoft.Office.Interop.Excel.Range cl45_1 = oSheet.get_Range("N3", "N3");
       cl45_1.Value2 = "CN";
       cl45_1.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl45_2 = oSheet.get_Range("O3", "O3");
       cl45_2.Value2 = "TD";
       cl45_2.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range cl45_3 = oSheet.get_Range("P3", "P3");
       cl45_3.Value2 = "QP";
       cl45_3.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clDTB = oSheet.get_Range("Q3", "Q3");
       clDTB.Value2 = "ĐTB";
       clDTB.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clHL = oSheet.get_Range("R3", "R3");
       clHL.Value2 = "HL";
       clHL.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clHK = oSheet.get_Range("S3", "S3");
       clHK.Value2 = "HK";
       clHK.ColumnWidth = 5.0;
       Microsoft.Office.Interop.Excel.Range clCP = oSheet.get_Range("T3", "T3");
       clCP.Value2 = "Nghi cp";
       clCP.ColumnWidth = 10.0;
       Microsoft.Office.Interop.Excel.Range clKP = oSheet.get_Range("U3", "U3");
       clKP.Value2 = "Nghi kp";
       clKP.ColumnWidth = 10.0;
       Microsoft.Office.Interop.Excel.Range clDH = oSheet.get_Range("V3", "V3");
       clDH.Value2 = "Danh hiệu";
       clDH.ColumnWidth = 20.0;


       Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "V3");
       rowHead.Font.Bold = true;
       // Kẻ viền
       rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
       // Thiết lập màu nền
       rowHead.Interior.ColorIndex = 15;
       rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;


       // Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
       // vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
       object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];

       //Chuyển dữ liệu từ DataTable vào mảng đối tượng
       for (int r = 0; r < dt.Rows.Count; r++)
       {
           DataRow dr = dt.Rows[r];
           for (int c = 0; c < dt.Columns.Count; c++)
           {
               arr[r, c] = dr[c];
           }
       }

       //Thiết lập vùng điền dữ liệu
       int rowStart = 4;
       int columnStart = 1;

       int rowEnd = rowStart + dt.Rows.Count - 1;
       int columnEnd = dt.Columns.Count;

       // Ô bắt đầu điền dữ liệu
       Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
       // Ô kết thúc điền dữ liệu
       Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
       // Lấy về vùng điền dữ liệu
       Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);

       //Điền dữ liệu vào vùng đã thiết lập
       range.Value2 = arr;
       //Microsoft.Office.Interop.Excel.Range foot = oSheet.get_Range("M1", "V1");
       //foot.MergeCells = true;
       //foot.Value2 = "Giỏi: " + lblGioi.Text + " " + "Khá: " + lblKha.Text + " TB: " + lblTrungBinh.Text + " Yếu: " + lblYe1.Text + " Kém: " + lblKem.Text;
       ////foot.Font.Bold = true;
       //foot.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
       // Kẻ viền
       range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
       // Căn giữa cột STT
       Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
       Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
       oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;



   }
   private readonly string[] VietnameseSigns = new string[]

    {

        "aAeEoOuUiIdDyY",

        "áàạảãâấầậẩẫăắằặẳẵ",

        "ÁÀẠẢÃÂẤẦẬẨẪĂẮẰẶẲẴ",

        "éèẹẻẽêếềệểễ",

        "ÉÈẸẺẼÊẾỀỆỂỄ",

        "óòọỏõôốồộổỗơớờợởỡ",

        "ÓÒỌỎÕÔỐỒỘỔỖƠỚỜỢỞỠ",

        "úùụủũưứừựửữ",

        "ÚÙỤỦŨƯỨỪỰỬỮ",

        "íìịỉĩ",

        "ÍÌỊỈĨ",

        "đ",

        "Đ",

        "ýỳỵỷỹ",

        "ÝỲỴỶỸ"

    };



   public string RemoveSign4VietnameseString(string str)
   {

       //Tiến hành thay thế , lọc bỏ dấu cho chuỗi

       for (int i = 1; i < VietnameseSigns.Length; i++)
       {

           for (int j = 0; j < VietnameseSigns[i].Length; j++)

               str = str.Replace(VietnameseSigns[i][j], VietnameseSigns[0][i - 1]);

       }

       return str;

   }

}
